from flask import Flask, request, render_template, redirect, url_for, session, flash,jsonify
from flask_mysqldb import MySQL
from flask_bcrypt import Bcrypt
import base64
from models import recommend_products
from flask_mail import Mail, Message
import json
from flask import jsonify
from datetime import datetime

app = Flask(__name__)
app.secret_key = 'secret_key'
ADMIN_KEY='dqz'
# MySQL数据库配置
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'duqianzhou7'
app.config['MYSQL_DB'] = 'shop'


# 邮件配置
app.config['MAIL_DEFAULT_SENDER'] = '2454938150@qq.com'
app.config['MAIL_SERVER'] = 'smtp.qq.com'
app.config['MAIL_PORT'] = 465  # 使用SSL端口
app.config['MAIL_USERNAME'] = '2454938150@qq.com'
app.config['MAIL_PASSWORD'] = 'kvjniebglqnieagd'  # QQ邮箱需要使用授权码
app.config['MAIL_USE_TLS'] = False
app.config['MAIL_USE_SSL'] = True
app.config['MAIL_DEBUG'] = 1

mail = Mail(app)

def send_email(subject, recipients, body):
    msg = Message(subject, sender='2454938150@qq.com', recipients=recipients)
    msg.body = body
    mail.send(msg)



mysql = MySQL(app)
bcrypt = Bcrypt(app)

def b64encode_filter(data):
    """将二进制数据转换为 base64 编码的字符串。"""
    if data:
        return base64.b64encode(data).decode('utf-8')
    return ""


app.jinja_env.filters['b64encode'] = b64encode_filter  # 注册过滤器
@app.route('/contact')
def contact():
    return render_template('contact.html')

@app.route('/submit_contact', methods=['POST'])
def submit_contact():
    name = request.form['name']
    email = request.form['email']
    message = request.form['message']

    # 将联系信息存储到数据库
    cursor = mysql.connection.cursor()
    cursor.execute("INSERT INTO contacts (name, email, message) VALUES (%s, %s, %s)", (name, email, message))
    mysql.connection.commit()
    cursor.close()

    flash('感谢您的联系，我们会尽快回复您。')
    return redirect(url_for('contact'))

@app.route('/admin', methods=['GET', 'POST'])
def admin_login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']

        cursor = mysql.connection.cursor()
        cursor.execute("SELECT * FROM admins WHERE username = %s", [username])
        admin = cursor.fetchone()
        cursor.close()

        if admin and bcrypt.check_password_hash(admin[2], password):  # 假设密码在返回的第三个位置
            session['admin_logged_in'] = True
            session['admin_username'] = username  # 保存用户名到会话中
            return redirect(url_for('admin_dashboard'))
        else:
            return render_template('admin.html', error="无效的用户名或密码")
    return render_template('admin.html')

@app.route('/admin_home')
def admin_home():
    if not session.get('admin_logged_in'):
        return redirect(url_for('admin_login'))
    return redirect(url_for('admin_dashboard'))

@app.route('/admin_profile', methods=['GET', 'POST'])
def admin_profile():
    if not session.get('admin_logged_in'):
        return redirect(url_for('admin_login'))

    admin_username = session.get('admin_username')

    if request.method == 'POST':
        full_name = request.form['full_name']
        email = request.form['email']
        role = request.form['role']
        password = request.form['password']

        cursor = mysql.connection.cursor()
        try:
            if password:
                hashed_password = bcrypt.generate_password_hash(password).decode('utf-8')
                cursor.execute("UPDATE admins SET full_name = %s, email = %s, role = %s, password = %s WHERE username = %s",
                               (full_name, email, role, hashed_password, admin_username))
            else:
                cursor.execute("UPDATE admins SET full_name = %s, email = %s, role = %s WHERE username = %s",
                               (full_name, email, role, admin_username))
            mysql.connection.commit()
            flash('信息更新成功')
        except Exception as e:
            mysql.connection.rollback()
            flash(f'更新信息时出错: {str(e)}')
        finally:
            cursor.close()
        return redirect(url_for('admin_profile'))

    cursor = mysql.connection.cursor()
    cursor.execute("SELECT username, full_name, email, role FROM admins WHERE username = %s", [admin_username])
    admin = cursor.fetchone()
    cursor.close()
    return render_template('admin_profile.html', admin=admin)

@app.route('/add_product', methods=['POST'])
def add_product():
    if not session.get('admin_logged_in'):
        return redirect(url_for('admin_login'))

    name = request.form['name']
    description = request.form['description']
    price = request.form['price']
    image = request.files['image']
    tags = request.form['category']  # 获取产品标签
    stock = request.form['stock']  # 获取库存数据

    if image:
        image_data = image.read()
    else:
        image_data = None

    try:
        cursor = mysql.connection.cursor()
        cursor.execute(
            "INSERT INTO products (name, description, price, image, category, stock) VALUES (%s, %s, %s, %s, %s, %s)",
            (name, description, price, image_data, tags, stock)
        )
        mysql.connection.commit()
        cursor.close()
        flash('产品添加成功！')
    except Exception as e:
        mysql.connection.rollback()
        cursor.close()
        flash(f'添加产品时出错: {str(e)}')

    return redirect(url_for('admin_dashboard'))


@app.route('/cart')
def cart():
    if 'username' not in session:
        return redirect(url_for('login'))

    username = session['username']  # 使用会话中的用户名
    cursor = mysql.connection.cursor()
    cursor.execute("""
        SELECT p.id, p.name, p.description, p.price, p.image, c.quantity, p.stock
        FROM cart c
        JOIN products p ON c.product_id = p.id
        JOIN users u ON c.username = u.username
        WHERE u.username = %s
    """, [username])
    cart_items = cursor.fetchall()
    cursor.close()
    return render_template('cart.html', cart_items=cart_items)

@app.route('/update_cart', methods=['POST'])
def update_cart():
    product_id = int(request.form['product_id'])
    new_quantity = int(request.form['quantity'])
    username = session['username']  # 使用会话中的用户名

    cursor = mysql.connection.cursor()
    cursor.execute("SELECT stock FROM products WHERE id = %s", [product_id])
    stock = cursor.fetchone()[0]

    if new_quantity <= stock:  # 检查新数量是否小于等于库存
        cursor.execute("""
            UPDATE cart
            SET quantity = %s
            WHERE product_id = %s AND username = %s
        """, (new_quantity, product_id, username))
        mysql.connection.commit()
        flash('购物车已更新')
    else:
        flash('库存不足，无法更新数量')
    cursor.close()
    return redirect(url_for('cart'))


@app.route('/orders')
def orders():
    if 'username' not in session:
        return redirect(url_for('login'))

    username = session['username']  # 使用会话中的用户名
    cursor = mysql.connection.cursor()

    cursor.execute("""
        SELECT o.order_id, p.name AS product_name, o.quantity, p.price, o.order_date, o.status
        FROM orders o
        JOIN users u ON o.customer_id = u.id
        JOIN products p ON o.product_id = p.id
        WHERE u.username = %s
        ORDER BY o.order_date DESC
    """, [username])

    orders = cursor.fetchall()
    cursor.close()

    orders_list = [
        {
            'order_id': order[0],
            'product_name': order[1],
            'quantity': order[2],
            'price': order[3],
            'order_date': order[4],
            'status': order[5]
        }
        for order in orders
    ]

    return render_template('orders.html', orders=orders_list)


@app.route('/place_order', methods=['POST'])
def place_order():
    if 'username' not in session:
        return redirect(url_for('login'))

    username = session['username']
    selected_product_ids = request.form.getlist('product_ids')
    quantities = request.form.getlist('quantity')

    if not selected_product_ids:
        flash('请选择要下单的商品')
        return redirect(url_for('cart'))

    cursor = mysql.connection.cursor()

    try:
        # 使用参数化查询来避免 SQL 注入
        format_strings = ','.join(['%s'] * len(selected_product_ids))
        cursor.execute(f"""
            SELECT p.id, p.name, p.price, c.quantity, p.stock, u.id AS user_id
            FROM cart c
            JOIN products p ON c.product_id = p.id
            JOIN users u ON c.username = u.username
            WHERE c.username = %s AND p.id IN ({format_strings})
        """, [username] + selected_product_ids)
        cart_items = cursor.fetchall()

        # 检查库存
        for item in cart_items:
            if item[3] > item[4]:  # 检查库存
                flash(f'产品 {item[1]} 库存不足，无法下单')
                cursor.close()
                return redirect(url_for('cart'))

        # 更新库存和流行度，并插入订单记录
        for item in cart_items:
            cursor.execute("""
                UPDATE products
                SET stock = stock - %s, popularity = popularity + %s
                WHERE id = %s
            """, (item[3], item[3], item[0]))

            cursor.execute("""
                INSERT INTO orders (customer_id, product_id, quantity, order_date, status)
                VALUES (%s, %s, %s, %s, %s)
            """, (item[5], item[0], item[3], datetime.now().date(), '已下单'))

        # 清空购物车中已下单的商品
        cursor.execute(f"""
            DELETE FROM cart
            WHERE username = %s AND product_id IN ({format_strings})
        """, [username] + selected_product_ids)

        mysql.connection.commit()
        flash('订单已成功提交，购物车已清空')

        # 发送邮件通知
        send_order_email(username, cart_items)

    except Exception as e:
        mysql.connection.rollback()
        flash(f'下单时出错: {str(e)}')
    finally:
        cursor.close()

    return redirect(url_for('cart'))


def send_email(subject, recipients, body):
    msg = Message(subject, recipients=recipients, sender=app.config['MAIL_DEFAULT_SENDER'])  # 指定发件人
    msg.body = body
    try:
        mail.send(msg)
        print("邮件发送成功")
    except Exception as e:
        print(f"发送邮件时出错: {e}")

def send_order_email(username, cart_items):
    try:
        cursor = mysql.connection.cursor()
        cursor.execute("SELECT email FROM users WHERE username = %s", [username])
        user_email = cursor.fetchone()[0]
        total_price = sum([item[2] * item[3] for item in cart_items])
        product_details = ""
        for item in cart_items:
            product_details += f"产品名称: {item[1]}, 数量: {item[3]}, 价格: {item[2]},总价: {item[2] * item[3]}\n"

        msg_body = f"用户 {username} 刚刚下了一个新订单:\n\n{product_details} \n     总金额：{total_price}\n\n请及时处理哦！\n\n谢谢！"
        send_email('新订单通知', ['2454938150@qq.com'], msg_body)
    except Exception as e:
        print(f"发送邮件时出错: {e}")


@app.route('/delete_from_cart/<int:product_id>', methods=['POST'])
def delete_from_cart(product_id):
    if 'username' not in session:
        return redirect(url_for('login'))

    username = session['username']  # 使用会话中的用户名
    cursor = mysql.connection.cursor()
    cursor.execute("""
        DELETE FROM cart
        WHERE product_id = %s AND username = %s
    """, (product_id, username))
    mysql.connection.commit()
    cursor.close()
    flash('商品已从购物车删除')
    return redirect(url_for('cart'))


@app.route('/sign_receipt', methods=['POST'])
def sign_receipt():
    if 'username' not in session:
        return jsonify(success=False, message='用户未登录')

    order_id = request.form['order_id']
    username = session['username']

    cursor = mysql.connection.cursor()

    try:
        cursor.execute("""
            SELECT o.order_id
            FROM orders o
            JOIN users u ON o.customer_id = u.id
            WHERE o.order_id = %s AND u.username = %s
        """, (order_id, username))

        order = cursor.fetchone()

        if order:
            cursor.execute("""
                UPDATE orders
                SET status = '已签收'
                WHERE order_id = %s
            """, [order_id])
            mysql.connection.commit()
            cursor.close()
            return jsonify(success=True)
        else:
            cursor.close()
            return jsonify(success=False, message='订单不存在或无权操作')
    except Exception as e:
        mysql.connection.rollback()
        cursor.close()
        return jsonify(success=False, message=str(e))


def get_all_products(order_by='name', order_dir='asc', page=1, per_page=5):
    cursor = mysql.connection.cursor()
    valid_columns = ['name', 'price', 'popularity', 'category', 'stock']
    if order_by not in valid_columns:
        order_by = 'name'  # 默认按名称排序

    order_dir = 'asc' if order_dir not in ['asc', 'desc'] else order_dir  # 默认排序方向

    offset = (page - 1) * per_page
    query = f"""
        SELECT id, name, description, price, category, stock, image, popularity 
        FROM products 
        ORDER BY {order_by} {order_dir} 
        LIMIT %s OFFSET %s
    """
    cursor.execute(query, (per_page, offset))
    products = cursor.fetchall()

    cursor.execute("SELECT COUNT(*) FROM products")
    total_products = cursor.fetchone()[0]
    cursor.close()

    product_list = [
        {
            'id': p[0], 'name': p[1], 'description': p[2], 'price': p[3],
            'category': p[4], 'stock': p[5], 'image': base64.b64encode(p[6]).decode('utf-8') if p[6] else None,
            'popularity': p[7]
        }
        for p in products
    ]
    return product_list, total_products

def get_categories():
    cursor = mysql.connection.cursor()
    cursor.execute("SELECT DISTINCT category FROM products")
    categories = cursor.fetchall()
    cursor.close()
    return [category[0] for category in categories]

def get_all_contacts():
    cursor = mysql.connection.cursor()
    cursor.execute("SELECT id, name, email, message, created_at FROM contacts")
    contacts = cursor.fetchall()
    cursor.close()
    return contacts

@app.route('/products')
def products_page():
    selected_category = request.args.get('category')
    order_by = request.args.get('order_by', 'name')  # 默认按名称排序
    order_dir = request.args.get('order_dir', 'asc')  # 默认升序
    page = request.args.get('page', 1, type=int)
    per_page = 5  # 每页显示的产品数量

    products, total_products = get_all_products(order_by, order_dir, page, per_page)

    if selected_category:
        filtered_products = [p for p in products if p['category'] == selected_category]
    else:
        filtered_products = products

    total_pages = (total_products + per_page - 1) // per_page

    recommended = recommend_products(products)
    categories = get_categories()

    return render_template('products.html', categories=categories, products=filtered_products, recommended=recommended, order_by=order_by, order_dir=order_dir, page=page, total_pages=total_pages)

@app.route('/add_to_cart', methods=['POST'])
def add_to_cart():
    if 'username' not in session:
        return redirect(url_for('login'))

    username = session['username']
    product_id = request.form['product_id']

    try:
        cursor = mysql.connection.cursor()
        # 获取用户ID
        cursor.execute("SELECT id FROM users WHERE username = %s", [username])
        user = cursor.fetchone()
        if user is None:
            flash('用户不存在。')
            return redirect(url_for('products'))

        user_id = user[0]

        # 检查产品是否存在
        cursor.execute("SELECT id FROM products WHERE id = %s", [product_id])
        product = cursor.fetchone()
        if product is None:
            flash('产品不存在。')
            return redirect(url_for('products'))

        # 将商品添加到购物车
        cursor.execute("""
            INSERT INTO cart (username, product_id, quantity)
            VALUES (%s, %s, 1)
            ON DUPLICATE KEY UPDATE quantity = quantity + 1
        """, (username, product_id))
        mysql.connection.commit()
        flash('商品已添加到购物车。')

        # 调试日志
        cursor.execute("SELECT * FROM cart WHERE username = %s", [username])
        cart_items = cursor.fetchall()
        print(f"用户 {username} 的购物车内容: {cart_items}")

    except Exception as e:
        mysql.connection.rollback()
        flash(f'添加到购物车时出错: {e}')
    finally:
        cursor.close()

    return redirect(url_for('cart'))

@app.route('/edit_product/<int:id>', methods=['GET', 'POST'])
def edit_product(id):
    if not session.get('admin_logged_in'):
        return redirect(url_for('admin_login'))

    if request.method == 'POST':
        name = request.form['name']
        description = request.form['description']
        price = request.form['price']
        category = request.form['category']
        stock = request.form['stock']  # 获取表单中的库存值
        image = request.files['image']
        image_data = image.read() if image else None

        try:
            cursor = mysql.connection.cursor()
            if image_data:
                cursor.execute("""
                    UPDATE products SET name=%s, description=%s, price=%s, category=%s, stock=%s, image=%s 
                    WHERE id=%s
                """, (name, description, price, category, stock, image_data, id))
            else:
                cursor.execute("""
                    UPDATE products SET name=%s, description=%s, price=%s, category=%s, stock=%s 
                    WHERE id=%s
                """, (name, description, price, category, stock, id))
            mysql.connection.commit()
            cursor.close()
            flash('产品更新成功。')
            return redirect(url_for('admin_dashboard'))
        except Exception as e:
            mysql.connection.rollback()
            flash('更新产品时发生错误：{}'.format(e))
            return redirect(url_for('edit_product', id=id))

    else:
        # Display the form for editing
        cursor = mysql.connection.cursor()
        cursor.execute("SELECT id, name, description, price, category, stock FROM products WHERE id = %s", [id])
        product = cursor.fetchone()
        cursor.close()

        if product:
            product_dict = {
                'id': product[0],
                'name': product[1],
                'description': product[2],
                'price': product[3],
                'category': product[4],
                'stock': product[5]
            }
            return render_template('edit_product.html', product=product_dict)
        else:
            flash('未找到该产品。')
            return redirect(url_for('admin_dashboard'))

@app.route('/delete_product/<int:id>')
def delete_product(id):
    if not session.get('admin_logged_in'):
        return redirect(url_for('admin_login'))

    cursor = mysql.connection.cursor()
    cursor.execute("DELETE FROM products WHERE id = %s", [id])
    mysql.connection.commit()
    cursor.close()

    return redirect(url_for('admin_dashboard'))



@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        cursor = mysql.connection.cursor()
        cursor.execute("SELECT * FROM users WHERE username=%s", [username])
        user = cursor.fetchone()
        cursor.close()
        if user and bcrypt.check_password_hash(user[2], password):  # 假设密码存储在返回的第三个位置
            session['username'] = username
            return redirect(url_for('home'))
        else:
            return render_template('login.html', error='账号或密码错误')
    return render_template('login.html')

@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        email = request.form['email']
        hashed_password = bcrypt.generate_password_hash(password).decode('utf-8')

        cursor = mysql.connection.cursor()
        # 检查数据库中是否已存在该用户名
        cursor.execute("SELECT * FROM users WHERE username=%s", (username,))
        existing_user = cursor.fetchone()
        if (existing_user):
            cursor.close()
            return render_template('register.css.html', error='用户已存在')

        # 如果不存在，插入新用户
        cursor.execute("INSERT INTO users (username, password, email) VALUES (%s, %s, %s)",
                       (username, hashed_password, email))
        mysql.connection.commit()
        cursor.close()

        return redirect(url_for('login'))
    return render_template('register.html')


@app.route('/')
def home():
    if 'username' in session:
        username = session['username']
        cursor = mysql.connection.cursor()
        cursor.execute(
            "SELECT id, name, description, price, image, popularity FROM products ORDER BY popularity DESC LIMIT 10")
        products = cursor.fetchall()

        cursor.execute("SELECT avatar FROM users WHERE username = %s", [username])
        avatar = cursor.fetchone()[0]

        cursor.close()

        product_list = [
            {'id': p[0], 'name': p[1], 'description': p[2], 'price': p[3], 'image': b64encode_filter(p[4]),
             'popularity': p[5]}
            for p in products
        ]

        recommended = recommend_products(product_list)

        return render_template('index.html', username=username, avatar=avatar, products=product_list,
                               recommended=recommended)
    return redirect(url_for('login'))


@app.route('/logout')
def logout():
    session.pop('username', None)
    return redirect(url_for('login'))

@app.route('/terms')
def terms():
    return render_template('terms.html')

@app.route('/profile', methods=['GET', 'POST'])
def profile():
    if 'username' not in session:
        return redirect(url_for('login'))

    username = session['username']
    cursor = mysql.connection.cursor()

    if request.method == 'POST':
        email = request.form['email']
        password = request.form['password']
        phone = request.form['phone']
        avatar = request.files['avatar']

        if avatar:
            avatar_data = avatar.read()
        else:
            avatar_data = None

        if password:
            hashed_password = bcrypt.generate_password_hash(password).decode('utf-8')
            if avatar_data:
                cursor.execute("""
                    UPDATE users SET email=%s, password=%s, phone=%s, avatar=%s WHERE username=%s
                """, (email, hashed_password, phone, avatar_data, username))
            else:
                cursor.execute("""
                    UPDATE users SET email=%s, password=%s, phone=%s WHERE username=%s
                """, (email, hashed_password, phone, username))
        else:
            if avatar_data:
                cursor.execute("""
                    UPDATE users SET email=%s, phone=%s, avatar=%s WHERE username=%s
                """, (email, phone, avatar_data, username))
            else:
                cursor.execute("""
                    UPDATE users SET email=%s, phone=%s WHERE username=%s
                """, (email, phone, username))

        mysql.connection.commit()
        cursor.close()
        flash('信息更新成功')
        return redirect(url_for('profile'))

    cursor.execute("SELECT username, email, phone, avatar FROM users WHERE username = %s", (username,))
    user = cursor.fetchone()
    cursor.close()
    return render_template('profile.html', user=user)

@app.route('/about')
def about():
    return render_template('about.html')


@app.route('/admin_register', methods=['GET', 'POST'])
def admin_register():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        admin_key = request.form['admin_key']

        if admin_key != ADMIN_KEY:
            return render_template('admin_register.html', error="管理员密钥无效")

        hashed_password = bcrypt.generate_password_hash(password).decode('utf-8')

        cursor = mysql.connection.cursor()
        cursor.execute("SELECT * FROM admins WHERE username = %s", [username])
        existing_admin = cursor.fetchone()

        if existing_admin:
            cursor.close()
            return render_template('admin_register.html', error="用户名已存在")

        cursor.execute("INSERT INTO admins (username, password) VALUES (%s, %s)", (username, hashed_password))
        mysql.connection.commit()
        cursor.close()

        return redirect(url_for('admin_login'))
    return render_template('admin_register.html')

@app.route('/admin_logout')
def admin_logout():
    session.pop('admin_logged_in', None)
    return redirect(url_for('admin_login'))


def admin_get_all_products(search_query='', order_by='name', order_dir='asc', page=1, per_page=5):
    cursor = mysql.connection.cursor()
    valid_columns = ['name', 'price', 'popularity', 'category', 'stock']
    if order_by not in valid_columns:
        order_by = 'name'  # 默认按名称排序

    order_dir = 'asc' if order_dir not in ['asc', 'desc'] else order_dir  # 默认排序方向

    offset = (page - 1) * per_page

    search_query = f"%{search_query}%"
    query = f"""
        SELECT id, name, description, price, category, stock, image, popularity 
        FROM products 
        WHERE name LIKE %s 
        ORDER BY {order_by} {order_dir} 
        LIMIT %s OFFSET %s
    """
    cursor.execute(query, (search_query, per_page, offset))
    products = cursor.fetchall()

    cursor.execute("SELECT COUNT(*) FROM products WHERE name LIKE %s", (search_query,))
    total_products = cursor.fetchone()[0]
    cursor.close()

    product_list = [
        {
            'id': p[0], 'name': p[1], 'description': p[2], 'price': p[3],
            'category': p[4], 'stock': p[5], 'image': base64.b64encode(p[6]).decode('utf-8') if p[6] else None,
            'popularity': p[7]
        }
        for p in products
    ]
    return product_list, total_products

@app.route('/privacy')
def privacy():
    return render_template('privacy.html')

@app.route('/admin_dashboard')
def admin_dashboard():
    if not session.get('admin_logged_in'):
        return redirect(url_for('admin_login'))

    search_query = request.args.get('search', '')
    page = request.args.get('page', 1, type=int)
    per_page = 5

    products, total_products = admin_get_all_products(search_query=search_query, page=page, per_page=per_page)

    # 获取管理员信息
    admin_username = session.get('admin_username')
    cursor = mysql.connection.cursor()
    cursor.execute("SELECT full_name, email, role FROM admins WHERE username = %s", (admin_username,))
    admin_info = cursor.fetchone()

    # 获取每日销量数据
    cursor.execute("""
        SELECT DATE(order_date) as date, SUM(quantity) as total_sales
        FROM orders
        WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        GROUP BY DATE(order_date)
        ORDER BY date
    """)
    sales_data = cursor.fetchall()
    cursor.close()

    # 处理销量数据
    dates = [row[0].strftime('%Y-%m-%d') for row in sales_data]
    sales = [float(row[1]) for row in sales_data]

    return render_template(
        'admin_dashboard.html',
        products=products,
        search_query=search_query,
        page=page,
        total_pages=(total_products + per_page - 1) // per_page,
        admin_info=admin_info,
        dates=json.dumps(dates),
        sales=json.dumps(sales)
    )

@app.route('/admin_contacts')
def admin_contacts():
    if not session.get('admin_logged_in'):
        return redirect(url_for('admin_login'))

    page = request.args.get('page', 1, type=int)
    per_page = 10

    cursor = mysql.connection.cursor()
    cursor.execute("SELECT COUNT(*) FROM contacts")
    total_contacts = cursor.fetchone()[0]

    cursor.execute("""
        SELECT id, name, email, message, created_at 
        FROM contacts 
        ORDER BY created_at DESC 
        LIMIT %s OFFSET %s
    """, (per_page, (page - 1) * per_page))
    contacts = cursor.fetchall()
    cursor.close()

    return render_template(
        'admin_contacts.html',
        contacts=contacts,
        page=page,
        total_pages=(total_contacts + per_page - 1) // per_page
    )

@app.route('/admin_orders')
def admin_orders():
    if not session.get('admin_logged_in'):
        return redirect(url_for('admin_login'))

    page = request.args.get('page', 1, type=int)
    per_page = 10

    cursor = mysql.connection.cursor()
    cursor.execute("SELECT COUNT(*) FROM orders")
    total_orders = cursor.fetchone()[0]

    cursor.execute("""
        SELECT o.order_id, o.customer_id, u.username, p.name, o.quantity, p.price, o.order_date, o.status
        FROM orders o
        JOIN users u ON o.customer_id = u.id
        JOIN products p ON o.product_id = p.id
        ORDER BY o.order_date DESC
        LIMIT %s OFFSET %s
    """, (per_page, (page - 1) * per_page))
    orders = cursor.fetchall()
    cursor.close()

    return render_template(
        'admin_orders.html',
        orders=orders,
        page=page,
        total_pages=(total_orders + per_page - 1) // per_page
    )

@app.route('/admin_sales')
def admin_sales():
    if not session.get('admin_logged_in'):
        return redirect(url_for('admin_login'))

    cursor = mysql.connection.cursor()
    cursor.execute("""
        SELECT DATE(order_date) as date, SUM(quantity) as total_sales
        FROM orders
        WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        GROUP BY DATE(order_date)
        ORDER BY date
    """)
    sales_data = cursor.fetchall()
    cursor.close()

    dates = [row[0].strftime('%Y-%m-%d') for row in sales_data]
    sales = [float(row[1]) for row in sales_data]

    return render_template('admin_sales.html', dates=dates, sales=sales)


if __name__ == '__main__':
    app.run(debug=True)
